<?php
/**
 * office
 */

namespace app\server;


use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class Excel {
    /**
     * 导出数据库中数据表的数据
     * @param string $expTitle
     * @param array $expCellName
     * @param array $expTableData
     * @throws \PhpOffice\PhpSpreadsheet\Exception
     * @throws \PhpOffice\PhpSpreadsheet\Writer\Exception
     */
    public function exportExcel(string $expTitle = '文件名称', array $expCellName = [], array $expTableData = []) {
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = $xlsTitle . date('YmdHis');         //or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        $objPHPExcel = new Spreadsheet();
        $cellName = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R'];

        $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(25);
        $objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(25);
//        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
//         $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));
        //处理表数据
        for ($i = 0; $i < $cellNum; $i++) {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '1', $expCellName[$i][1]);
        }
        // Miscellaneous glyphs, UTF-8
        for ($i = 0; $i < $dataNum; $i++) {
            for ($j = 0; $j < $cellNum; $j++) {
                $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j] . ($i + 2),
                    ' ' . $expTableData[$i][$expCellName[$j][0]]);
            }
        }
        header('Content-Type: application/vnd.ms-excel');
        header("Content-Disposition: attachment;filename=" . $fileName . ".xls");
        header('Cache-Control: max-age=0');
        $objWriter = IOFactory::createWriter($objPHPExcel, 'Xls');
        $objWriter->save('php://output');
        exit;
    }

    public function importExcel($file){
        //获取文件后缀
        $extension = $file->getOriginalExtension();
        if ('xlsx' == strtolower($extension)) {
            $objPHPExcel = IOFactory::createReader('Xlsx');
        } else {
            $objPHPExcel = IOFactory::createReader('Xls');
        }
        //读取文件数据
        $objContent = $objPHPExcel->load($file);
        $sheet = $objContent->getSheet(0);
        $highestRow = $sheet->getHighestRow();//行数
        if ($highestRow <= 0) {
            return error('无数据');
        }
        //循环读取excel表格，整合成数组。如果是不指定key的二维，就用$data[i][j]表示。
        $data=[];
        for ($j = 1; $j <= $highestRow; $j++) {
            $data[] = [
                'true_name' => trim($objContent->getActiveSheet()->getCell("A" . $j)->getValue()),
                'phone' => trim($objContent->getActiveSheet()->getCell("B" . $j)->getValue()),
                'address' => trim($objContent->getActiveSheet()->getCell("C" . $j)->getValue()),
            ];
        }
        return $data;
    }
}